package bulk_param.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import bulk_param.modele.OuverturePointModel;


public class SelectBoxOpenNumSpeDao {
	
	public OuverturePointModel ouverturePointModel;
	private String userid = "arbor";
    private String password = "arbor123";
    private String pilote = "oracle.jdbc.driver.OracleDriver";
    private String url = "jdbc:oracle:thin:@10.1.3.136:1522:cc_cust";

	private static Connection con;

     // constructor 
	public SelectBoxOpenNumSpeDao(){
		this.ouverturePointModel= new OuverturePointModel();
	}

	public Connection getConnection(){
				
		try {
			Class.forName(pilote);	

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
			con = DriverManager.getConnection(url, userid, password);
			System.out.println ("Ouverture Connection");     
		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		
		return con;
	}
	
	static  public  void  closeConnection(){         
		System.out.println ("Colse DBConnection.");          
		        
				
		try {
			con.close();             
			System.out.println ("Fermeture Connection");        
			}         
		catch (Exception ex) {             
			System.out.println ("ERROR :"+ex.getMessage());             
			ex.printStackTrace();        
			}    
		} 
	
			
	public ArrayList<String> listTarification(){
	
		ArrayList <String> listTarifs = new ArrayList<String>();
		try{
			String sql="SELECT DISTINCT d.description_text "
				+"FROM DESCRIPTIONS d, JURISDICTIONS j "
				+"WHERE j.description_code = d.description_code "
				+"AND j.JURISDICTION_CLASS IN (71,72) "
				+"AND d.description_code not in (5286,32666,32755,5292,"
				+"32665,32754,32809,32829,32830,32831,3638,3643) "
				+"AND d.language_code = 2 ";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			while(rs.next()){  
				listTarifs.add(rs.getString("description_text"));	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return listTarifs;
	}
}
	
